package dao;

import dto.ObraSocialPlanDTO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author lucifer
 */
public class ObraSocialPlanDAO {

  public static final String Tab =
    "create table obra_social_plan (" +
    "id serial," + //pk
    "id_obra_social int," + //fk
    "nombre text"+
    ");" +

    "alter table obra_social_plan add constraint obra_social_plan_pk primary key (id);" +
    "alter table obra_social_plan add constraint obra_social_plan_fk foreign key (id_obra_social) references obra_social(id);"
  ;
  
  public static final String Tuples =
    "insert into obra_social_plan (id, id_obra_social, nombre) values" +
    "(0, 0, 'Particular (sin obra social)');" ;

    /*
    "insert into obra_social_plan (id_obra_social, nombre) values" +
    "(1, 'Plan Small')," +
    "(1, 'Plan Medium')," +
    "(1, 'Plan Large')," +
    "(2, 'Plan Light')," +
    "(2, 'Plan Regular')," +
    "(3, 'Plan Económico')," +
    "(3, 'Plan Ahorro')," +
    "(3, 'Plan Cuidado')," +
    "(3, 'Plan Básico')," +
    "(4, 'Plan One')," +
    "(4, 'Plan Two')," +
    "(4, 'Plan Three')," +
    "(4, 'Plan Four')," +
    "(4, 'Plan Five')," +
    "(4, 'Plan Six')," +
    "(4, 'Plan Seven')," +
    "(4, 'Plan Eight')," +
    "(4, 'Plan Nine')," +
    "(4, 'Plan Ten')," +
    "(5, 'Plan Único')," +
    "(6, 'Plan Chico')," +
    "(6, 'Plan Mediano')," +
    "(6, 'Plan Grande')," +
    "(7, 'Plan Full')," +
    "(7, 'Plan Ultra');"
  ;
  */

  private static ObraSocialPlanDAO dao = null;
  private final Connection c;

  public static ObraSocialPlanDAO createDAO() throws SQLException  {
    if (dao == null) {
      dao = new ObraSocialPlanDAO();
    }
    return dao;
  }

  private ObraSocialPlanDAO() throws SQLException {
    c = DB.PQ().Conn();
  }

  public int LastID() throws SQLException {
    return DB.PQ().LastIDFrom("obra_social_plan");
  }

  public ArrayList<ObraSocialPlanDTO> All(int idObraSocial) throws SQLException {
    String query = String.format(
      "select * from obra_social_plan where id_obra_social = %d order by nombre;", idObraSocial
    );
    return getObraSocialPlanDTOs(query);
  }

  public ArrayList<ObraSocialPlanDTO> SearchByNombre(String s) throws SQLException {
    return getObraSocialPlanDTOs(queryByNombre(s));
  }

  private String queryByNombre(String name) throws SQLException {
    String query = String.format(
      "select * from obra_social_plan where nombre = '%s' order by nombre;",
      name
    );
    return query;
  }

  private ArrayList<ObraSocialPlanDTO> getObraSocialPlanDTOs(String query) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    ArrayList<ObraSocialPlanDTO> dtos = new ArrayList<>();
    while (r.next()) {
      dtos.add(
        new ObraSocialPlanDTO(
          r.getInt("id"),
          r.getInt("id_obra_social"),
          r.getString("nombre")
        )
      );
    }
    r.close();
    s.close();
    return dtos;
  }

  public ObraSocialPlanDTO ObraSocialPlanDTO(int id) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(
      "select * from obra_social_plan where id = " + id
    );
    ObraSocialPlanDTO dto = null;
    if (r.next()) {
      dto = new ObraSocialPlanDTO(
        r.getInt("id"),
        r.getInt("id_obra_social"),
        r.getString("nombre")
      );
    }
    r.close();
    s.close();
    return dto; //si no existe devuelve null
  }

  // Guarda un **nuevo** plan de una obra social.
  public int Save(ObraSocialPlanDTO p) throws SQLException {
    String query = String.format(
      "insert into obra_social_plan values (default, %d, '%s');",
      p.getIdObraSocial(), p.getNombre()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return LastID();
  }

  public void Delete(int id) throws SQLException {
    Statement s = c.createStatement();
    s.executeUpdate("delete from obra_social_plan where id = " + id);
    s.close();
  }

  // FIXME LATER
  /*
  public ObraSocialPlanDTO EditString(int id, String attr, String str) throws SQLException {
    String query = String.format(
      "update obra_social_plan set %s = '%s' where id = %d;", //FIXME
      attr, str, id
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return ObraSocialPlanDTO(id);
  }
  */

}
